// 产品分类模型
const { postgresPool } = require('../config/database');

class ProductCategory {
    constructor(data) {
        this.id = data.id;
        this.name_en = data.name_en;
        this.name_zh = data.name_zh;
        this.name_de = data.name_de;
        this.name_fr = data.name_fr;
        this.name_es = data.name_es;
        this.description = data.description;
        this.parent_id = data.parent_id;
        this.sort_order = data.sort_order;
        this.status = data.status;
        this.created_at = data.created_at;
        this.updated_at = data.updated_at;
    }

    // 创建分类
    static async create(categoryData) {
        const client = await postgresPool.connect();
        
        try {
            const query = `
                INSERT INTO product_categories (
                    name_en, name_zh, name_de, name_fr, name_es, 
                    description, parent_id, sort_order, status
                ) VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9)
                RETURNING *
            `;
            
            const values = [
                categoryData.name_en,
                categoryData.name_zh || null,
                categoryData.name_de || null,
                categoryData.name_fr || null,
                categoryData.name_es || null,
                categoryData.description || null,
                categoryData.parent_id || null,
                categoryData.sort_order || 0,
                categoryData.status || 'active'
            ];
            
            const result = await client.query(query, values);
            return new ProductCategory(result.rows[0]);
        } finally {
            client.release();
        }
    }

    // 根据ID查找分类
    static async findById(id) {
        const client = await postgresPool.connect();
        
        try {
            const query = 'SELECT * FROM product_categories WHERE id = $1';
            const result = await client.query(query, [id]);
            
            if (result.rows.length === 0) {
                return null;
            }
            
            return new ProductCategory(result.rows[0]);
        } finally {
            client.release();
        }
    }

    // 获取所有分类
    static async findAll(options = {}) {
        const client = await postgresPool.connect();
        
        try {
            const {
                page = 1,
                limit = 50,
                status = '',
                parent_id = null,
                sortBy = 'sort_order',
                sortOrder = 'ASC'
            } = options;
            
            let whereConditions = [];
            let values = [];
            let paramCount = 1;
            
            if (status) {
                whereConditions.push(`status = $${paramCount++}`);
                values.push(status);
            }
            
            if (parent_id !== undefined) {
                if (parent_id === null) {
                    whereConditions.push('parent_id IS NULL');
                } else {
                    whereConditions.push(`parent_id = $${paramCount++}`);
                    values.push(parent_id);
                }
            }
            
            const whereClause = whereConditions.length > 0 
                ? `WHERE ${whereConditions.join(' AND ')}` 
                : '';
            
            // 获取总数
            const countQuery = `SELECT COUNT(*) FROM product_categories ${whereClause}`;
            const countResult = await client.query(countQuery, values);
            const total = parseInt(countResult.rows[0].count);
            
            // 获取数据
            const offset = (page - 1) * limit;
            const dataQuery = `
                SELECT * FROM product_categories 
                ${whereClause}
                ORDER BY ${sortBy} ${sortOrder}
                LIMIT $${paramCount} OFFSET $${paramCount + 1}
            `;
            
            const dataValues = [...values, limit, offset];
            const dataResult = await client.query(dataQuery, dataValues);
            
            const categories = dataResult.rows.map(row => new ProductCategory(row));
            
            return {
                data: categories,
                pagination: {
                    page: parseInt(page),
                    limit: parseInt(limit),
                    total,
                    totalPages: Math.ceil(total / limit)
                }
            };
        } finally {
            client.release();
        }
    }

    // 获取分类树（包含子分类）
    static async getCategoryTree() {
        const client = await postgresPool.connect();
        
        try {
            // 获取所有分类
            const query = `
                SELECT id, name_en, name_zh, name_de, name_fr, name_es, 
                       description, parent_id, sort_order, status,
                       created_at, updated_at
                FROM product_categories 
                WHERE status = 'active'
                ORDER BY sort_order, name_en
            `;
            
            const result = await client.query(query);
            
            // 构建树形结构
            const categories = result.rows;
            const categoryMap = new Map();
            const tree = [];
            
            // 创建映射
            categories.forEach(category => {
                categoryMap.set(category.id, { ...category, children: [] });
            });
            
            // 构建树
            categories.forEach(category => {
                const node = categoryMap.get(category.id);
                if (category.parent_id === null) {
                    tree.push(node);
                } else {
                    const parent = categoryMap.get(category.parent_id);
                    if (parent) {
                        parent.children.push(node);
                    }
                }
            });
            
            return tree;
        } finally {
            client.release();
        }
    }

    // 更新分类
    async update(updateData) {
        const client = await postgresPool.connect();
        
        try {
            const fields = [];
            const values = [];
            let paramCount = 1;
            
            if (updateData.name_en !== undefined) {
                fields.push(`name_en = $${paramCount++}`);
                values.push(updateData.name_en);
            }
            
            if (updateData.name_zh !== undefined) {
                fields.push(`name_zh = $${paramCount++}`);
                values.push(updateData.name_zh);
            }
            
            if (updateData.name_de !== undefined) {
                fields.push(`name_de = $${paramCount++}`);
                values.push(updateData.name_de);
            }
            
            if (updateData.name_fr !== undefined) {
                fields.push(`name_fr = $${paramCount++}`);
                values.push(updateData.name_fr);
            }
            
            if (updateData.name_es !== undefined) {
                fields.push(`name_es = $${paramCount++}`);
                values.push(updateData.name_es);
            }
            
            if (updateData.description !== undefined) {
                fields.push(`description = $${paramCount++}`);
                values.push(updateData.description);
            }
            
            if (updateData.parent_id !== undefined) {
                fields.push(`parent_id = $${paramCount++}`);
                values.push(updateData.parent_id);
            }
            
            if (updateData.sort_order !== undefined) {
                fields.push(`sort_order = $${paramCount++}`);
                values.push(updateData.sort_order);
            }
            
            if (updateData.status !== undefined) {
                fields.push(`status = $${paramCount++}`);
                values.push(updateData.status);
            }
            
            if (fields.length === 0) {
                return this;
            }
            
            fields.push(`updated_at = CURRENT_TIMESTAMP`);
            values.push(this.id);
            
            const query = `
                UPDATE product_categories 
                SET ${fields.join(', ')}
                WHERE id = $${paramCount}
                RETURNING *
            `;
            
            const result = await client.query(query, values);
            
            // 更新当前实例
            Object.assign(this, result.rows[0]);
            return this;
        } finally {
            client.release();
        }
    }

    // 删除分类
    async delete() {
        const client = await postgresPool.connect();
        
        try {
            // 检查是否有子分类
            const checkQuery = 'SELECT COUNT(*) FROM product_categories WHERE parent_id = $1';
            const checkResult = await client.query(checkQuery, [this.id]);
            
            if (parseInt(checkResult.rows[0].count) > 0) {
                throw new Error('无法删除包含子分类的分类');
            }
            
            // 检查是否有产品使用此分类
            const productQuery = 'SELECT COUNT(*) FROM products WHERE category_id = $1';
            const productResult = await client.query(productQuery, [this.id]);
            
            if (parseInt(productResult.rows[0].count) > 0) {
                throw new Error('无法删除有产品使用的分类');
            }
            
            const deleteQuery = 'DELETE FROM product_categories WHERE id = $1';
            await client.query(deleteQuery, [this.id]);
            
            return true;
        } finally {
            client.release();
        }
    }
}

module.exports = ProductCategory;